package com.iplatform.commons.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.util.StringUtils;

/**
 *
 * @ClassName: ExcelUtil
 * @Description: 导出工具类
 * @author: zhouyuhua
 * @date 2019年9月20日 上午9:33:53
 *
 */
public class ExcelUtil {

	/**
	 * 导出excel
	 *
	 * @param path     模板文件地址
	 * @param dataList 数据集合，集合中对象字段的顺序需要和excel模板字段的顺序相同
	 * @param fields   dataList中需要排除的字段，字段用逗号隔开
	 * @return
	 */
	public static Workbook exportExcel(String path, List<Map<String, String>> dataList, String fields) {
		InputStream in = null;
		try {
			in = new FileInputStream(new File(path));
			final Workbook workBook = WorkbookFactory.create(in);
			// 默认填第一个sheet
			Sheet sheet = workBook.getSheetAt(0);
			// 文件内容行数
			int rowNum = sheet.getLastRowNum();
			// 循环每一行
			for (Map<String, String> map : dataList) {
				// 获取当前行
				Row row = sheet.createRow(rowNum);
				// 将这个实体类填这一行
				setFieldValueMap(map, row, fields);
				// 每行增长
				rowNum++;
			}
			return workBook;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (in != null) {
					in.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	public static void setFieldValueMap(Map<String, String> map, Row row, String fields) throws Exception {
		int i = 0;
		// 遍历map中的值
		for (String value : map.values()) {
			Cell cell = row.createCell(i);
			if (value == null) {
				cell.setCellValue("");
			} else {
				if (formatValidate(value.toString(), "^[-\\+]?[.\\d]*$")) {
					if (value.toString().equals("-") || value.toString().contains("%")) {
						cell.setCellValue(value.toString());
					} else {
						cell.setCellValue(Double.parseDouble(value.toString()));
					}
				} else {
					cell.setCellValue(value.toString());
				}
			}
			i++;
		}
	}

	/**
	 * 导出excel
	 *
	 * @param path     模板文件地址
	 * @param dataList 数据集合，集合中对象字段的顺序需要和excel模板字段的顺序相同
	 * @param headArr  表头集合
	 * @param fields   dataList中需要排除的字段，字段用逗号隔开
	 * @return
	 */
	public static <T> Workbook exportExcel(String path, List<T> dataList, String[] headArr, String fields) {
		InputStream in = null;
		try {
			in = new FileInputStream(new File(path));
			final Workbook workBook = WorkbookFactory.create(in);
			// 默认填第一个sheet
			Sheet sheet = workBook.getSheetAt(0);
			// 文件内容行数
			int rowNum = sheet.getLastRowNum();
			// 循环表头
			Row r = sheet.createRow(rowNum);
			for (int j = 0; j < headArr.length; j++) {
				Cell cell = r.createCell(j);
				if (headArr[j] == null) {
					cell.setCellValue("");
				} else {
					cell.setCellValue(headArr[j]);
				}
			}
			rowNum++;
			// 循环每一行
			for (Object object : dataList) {
				// 获取当前行
				Row row = sheet.createRow(rowNum);
				// 将这个实体类填这一行
				setFieldValue(object, row, fields);
				// 每行增长
				rowNum++;
			}
			return workBook;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (in != null) {
					in.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	public static void setFieldValue(Object model, Row row, String fields) throws Exception {

		// 获取实体类的所有属性
		Field[] field = model.getClass().getDeclaredFields();
		List<Field> list = new ArrayList<>(Arrays.asList(field));
		Iterator<Field> it = list.iterator();
		// 筛选掉不需要的字段
		while (it.hasNext()) {
			String name = it.next().getName();
			if (!StringUtils.isEmpty(fields)) {
				String[] removeFieldsArray = fields.split(",");
				boolean isExist = ArrayUtils.contains(removeFieldsArray, name);
				if (isExist) {
					it.remove();
				}
			}
		}

		for (int i = 0; i < list.size(); i++) {
			String name = list.get(i).getName();
			// 将属性的首字符大写，方便构造get，set方法
			name = name.substring(0, 1).toUpperCase() + name.substring(1);
			Method m = model.getClass().getMethod("get" + name);
			Object invoke = m.invoke(model);

			// 获取这一行的每一列
			Cell cell = row.createCell(i);
			if (invoke == null) {
				cell.setCellValue("");
			} else {
				if (formatValidate(invoke.toString(), "^[-\\+]?[.\\d]*$")) {
					if (invoke.toString().equals("-") || invoke.toString().contains("%")) {
						cell.setCellValue(invoke.toString());
					} else {
						cell.setCellValue(Double.parseDouble(invoke.toString()));
					}
				} else {
					cell.setCellValue(invoke.toString());
				}
			}
		}
	}

	public static boolean formatValidate(String str, String formatStr) {
		Pattern pattern = Pattern.compile(formatStr);
		Matcher matcher = pattern.matcher((CharSequence) str);
		return matcher.matches();
	}

	public static boolean isValidDate(String str) {
		boolean convertSuccess = true;
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		try {
			format.setLenient(false);
			format.parse(str);
		} catch (Exception e) {
			convertSuccess = false;
		}
		return convertSuccess;
	}

	public static boolean isNumber(Object value) {
		// 判断小数点后2位的数字的正则表达式
		java.util.regex.Pattern pattern = java.util.regex.Pattern
				.compile("^(([1-9]{1}\\d*)|([0]{1}))(\\.(\\d){0,2})?$");
		java.util.regex.Matcher match = pattern.matcher(value.toString());
		if (match.matches() == false) {
			return false;
		} else {
			return true;
		}
	}

	public static String mateBrowser(HttpServletRequest request, String filename) throws Exception {
		String finalFileName = null;
		String userAgent = request.getHeader("USER-AGENT");
		// IE浏览器
		if ("MSIE".equals(userAgent)) {
			finalFileName = URLEncoder.encode(filename, "UTF8");
			// google,火狐浏览器
		} else if ("Mozilla".equals(userAgent)) {
			finalFileName = new String(filename.getBytes(), "ISO8859-1");
			// 其他浏览器
		} else {
			finalFileName = URLEncoder.encode(filename, "UTF8");
		}
		return finalFileName;
	}

}
